package com.jsc.common.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Optional;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.jfinal.plugin.activerecord.Db;
import com.jsc.common.model.Ticket;

public class ExcelKit {
	
	//参数为文件位置
		public static void processExcel(String filePath) throws FileNotFoundException, IOException{
			
			
				
				
				POIFSFileSystem fileSystem = new POIFSFileSystem(new FileInputStream(new File(filePath)));
				HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);					
				HSSFSheet sheet = workbook.getSheetAt(0);
				
				
				HSSFRow row = null;
				Ticket ticket = null;
				
				ArrayList<Ticket> ticketList = new ArrayList<Ticket>();
				
				//从第一行开始，跨过标题行
				for(int i = 1; i<sheet.getLastRowNum(); i++){
					row = sheet.getRow(i);
					ticket = new Ticket();
					
					Optional<HSSFCell> Cell = Optional.ofNullable(row.getCell(0));
					if(!Cell.isPresent()){
						System.out.println("表格为空，请查看Cell索引是否从1开始");
					}
					
					
					//初始化Ticket对象
					ticket.setId(UUIDUtil.generate());
					ticket.setEmpno(getCellStringValue(Cell.get()));
					
					
					
					
					
					ticketList.add(ticket);
				}
				
				
				String sql = "insert into tbl_ticket(id, empno)"
								+ " values(?, ?)";
				
				
				//批量写入
				Db.batch(sql
						, "id, empno"
						, ticketList
						, 300);
				
				
				
				workbook.close();
				
			
		}
		
		
		

		// 获取单元格内不同类型值
		private static String getCellStringValue(HSSFCell cell) {
			String cellValue = "";
			
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING:
				cellValue = cell.getStringCellValue();
				if (cellValue.trim().equals("") || cellValue.trim().length() <= 0)
					cellValue = " ";
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				cellValue = String.valueOf(cell.getNumericCellValue());
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
				cellValue = String.valueOf(cell.getNumericCellValue());
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				cellValue = " ";
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				break;
			case HSSFCell.CELL_TYPE_ERROR:
				break;
			default:
				break;
			}
			return cellValue.replaceAll(" ", "");
		}
}
